package com.yuanqi.repository;

import com.yuanqi.domain.AccountsPayableNew;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.time.LocalDate;
import java.util.List;

@Repository
public interface AccountsPayableNewRepository extends JpaRepository<AccountsPayableNew, Long> {
    
    // 根据供应商名称模糊查询
    List<AccountsPayableNew> findBySupplierNameContaining(String supplierName);
    
    // 根据逾期状态查询
    List<AccountsPayableNew> findByOverdueStatus(Integer overdueStatus);
    
    // 根据到期日期范围查询
    List<AccountsPayableNew> findByDueDateBetween(LocalDate startDate, LocalDate endDate);
    
    // 根据付款状态查询
    List<AccountsPayableNew> findByPaymentStatus(Integer paymentStatus);
    
    // 根据供应商ID查询
    List<AccountsPayableNew> findBySupplierId(Long supplierId);
    
    // 分页查询，支持搜索条件
    @Query("SELECT ap FROM AccountsPayableNew ap WHERE " +
           "(:supplierName IS NULL OR ap.supplierName LIKE %:supplierName%) AND " +
           "(:overdueStatus IS NULL OR ap.overdueStatus = :overdueStatus) AND " +
           "(:dueDate IS NULL OR ap.dueDate = :dueDate)")
    Page<AccountsPayableNew> findBySearchCriteria(
        @Param("supplierName") String supplierName,
        @Param("overdueStatus") Integer overdueStatus,
        @Param("dueDate") LocalDate dueDate,
        Pageable pageable
    );
    
    // 获取总应付金额
    @Query("SELECT COALESCE(SUM(ap.totalAmount), 0) FROM AccountsPayableNew ap")
    BigDecimal getTotalPayableAmount();
    
    // 获取总已付金额
    @Query("SELECT COALESCE(SUM(ap.paidAmount), 0) FROM AccountsPayableNew ap")
    BigDecimal getTotalPaidAmount();
    
    // 获取总未付金额
    @Query("SELECT COALESCE(SUM(ap.unpaidAmount), 0) FROM AccountsPayableNew ap")
    BigDecimal getTotalUnpaidAmount();
    
    // 获取逾期总金额
    @Query("SELECT COALESCE(SUM(ap.unpaidAmount), 0) FROM AccountsPayableNew ap WHERE ap.overdueStatus = 1")
    BigDecimal getTotalOverdueAmount();
    
    // 获取供应商数量
    @Query("SELECT COUNT(DISTINCT ap.supplierId) FROM AccountsPayableNew ap")
    Long getSupplierCount();
    
    // 获取本月付款金额
    @Query("SELECT COALESCE(SUM(ap.paidAmount), 0) FROM AccountsPayableNew ap WHERE " +
           "YEAR(ap.updateTime) = YEAR(CURRENT_DATE) AND MONTH(ap.updateTime) = MONTH(CURRENT_DATE)")
    BigDecimal getThisMonthPaymentAmount();
}
